package com.hy.dao;

import com.hy.utils.DBManager;
import com.hy.vo.ReportVo;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.util.List;

/**
 * @author zhangzhengwei2006
 * @description 针对表【report(举报理由表)】的数据库操作Mapper
 * @createDate 2024-01-09 10:15:07
 * @Entity com.hy.bean.report
 */
public class ReportDao {
    private QueryRunner queryRunner = new QueryRunner();


    // 分页查询举报帖子
    /*public List<ReportVo> selectPage(Integer currentPage, Integer pageSize) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select re.id,re.report_reason,ca.id as 'cid',ca.title,ca.card_content,ca.review_status,us.user_name,us2.user_name as 'informer',re.reportingType,re.restate from report re left join card ca on re.cardid = ca.id left join ums_user us on re.umsuserid = re.umsuserid and us.id = re.informer left join ums_user us2 on us2.id = re.informer order by re.restate limit ?,?";
        List<ReportVo> cardList = queryRunner.query(conn, sql, new BeanListHandler<>(ReportVo.class),(currentPage-1)*pageSize,pageSize);

        DbUtils.close(conn);
        return cardList;
    }*/
    // 分页查询举报帖子
    public List<ReportVo> selectCartPage(Integer currentPage, Integer pageSize) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select re.id,re.report_reason,ca.id as 'cid',ca.title,ca.card_content,ca.review_status,us.user_name,us2.user_name as 'informer',re.reportingType,re.restate from report re left join card ca on re.cardid = ca.id left join ums_user us on re.umsuserid = re.umsuserid and us.id = re.informer left join ums_user us2 on us2.id = re.informer where re.restate = '0'  and re.reportingType = '1' limit ?,?";
        List<ReportVo> cardList = queryRunner.query(conn, sql, new BeanListHandler<>(ReportVo.class), (currentPage - 1) * pageSize, pageSize);

        DbUtils.close(conn);
        return cardList;
    }

    // 查询帖子总条数
    public Long selectCardTotal() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select count(re.id) from report re left join card ca on re.cardid = ca.id where re.restate = '0' and re.reportingType = '1'";
        Long countNum = queryRunner.query(conn, sql, new ScalarHandler<>());
        DbUtils.close(conn);
        return countNum;
    }

    // 查询被举报的用户并分页
    public List<ReportVo> selectUserPage(Integer currentPage, Integer pageSize) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select re.id,re.report_reason,us.id as 'uid',us.user_name,us.user_status,us2.user_name as 'informer',re.reportingType,re.restate from report re left join ums_user us on re.umsuserid = us.id left join ums_user us2 on us2.id = re.informer where re.restate = '0' and re.reportingType = '0' limit ?,?";
        List<ReportVo> cardList = queryRunner.query(conn, sql, new BeanListHandler<>(ReportVo.class), (currentPage - 1) * pageSize, pageSize);

        DbUtils.close(conn);
        return cardList;
    }

    // 查询被举报的评论并分页
    public List<ReportVo> selectCommentPage(Integer currentPage, Integer pageSize) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select re.id,re.report_reason,us.id as 'uid',us.user_name,us.user_status,us2.user_name as 'informer',re.reportingType,re.restate, co.id as 'coid',co.comment_content from report re left join ums_user us on re.umsuserid = us.id left join ums_user us2 on us2.id = re.informer join comment co on re.commentid = co.id where re.restate = '0' and re.reportingType = '2' and co.tombstone = '0' limit ?,?";
        List<ReportVo> cardList = queryRunner.query(conn, sql, new BeanListHandler<>(ReportVo.class), (currentPage - 1) * pageSize, pageSize);

        DbUtils.close(conn);
        return cardList;
    }

    // 查询用户总条数
    public Long selectUserTotal() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select count(re.id) from report re left join card ca on re.cardid = ca.id where re.restate = '0' and re.reportingType = '0'";
        Long countNum = queryRunner.query(conn, sql, new ScalarHandler<>());
        DbUtils.close(conn);
        return countNum;
    }

    // 查询评论总条数
    public Long selectCommentTotal() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select count(re.id) from report re left join comment co on re.commentid = co.id where re.restate = '0' and re.reportingType = '0'";
        Long countNum = queryRunner.query(conn, sql, new ScalarHandler<>());
        DbUtils.close(conn);
        return countNum;
    }

    // 修改状态
    public int UpdateRestate(Integer rid) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "update report set restate = '1' where id = ?";
        int update = queryRunner.update(conn, sql, rid);
        DbUtils.close(conn);
        return update;
    }

    // 逻辑删除
    public int deaeteByid(Integer rid,Connection conn) throws Exception {
//        conn.setAutoCommit(false);
        String sql = "update report set tombstone = '1' where id = ?";
        int update = queryRunner.update(conn, sql, rid);
        return update;
    }

    // 修改状态
   /* public int UpdateAllUserRestate(Integer uid) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "update report set restate = '1' where id in (select id from (select id from report where umsuserid = ? and reportingType = '0')as tim)";
        int update = queryRunner.update(conn, sql, uid);
        DbUtils.close(conn);
        return update;
    }*/

}




